package com.hsnn.medstgmini.trading.pur.dao.impl;

import java.math.BigInteger;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.trading.pur.dao.PurOrderInfoOldDao;
import com.hsnn.medstgmini.trading.pur.model.OrderDetailStatus;
import com.hsnn.medstgmini.trading.pur.model.OrderPackStatus;
import com.hsnn.medstgmini.trading.pur.model.form.OrderPackage;
import com.hsnn.medstgmini.util.AreaUtil;
import com.hsnn.medstgmini.util.DateUtil;
import com.hsnn.medstgmini.util.Pagination;

@Repository
public class PurOrderInfoOldDaoImpl implements PurOrderInfoOldDao {

	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	
	@Autowired
	private HibernateSqlUtil dao;

	/**
	 * @category 历史订单查询[中心端]
	 * @author 应晓川
	 * @date 2015年7月10日
	 */
	@Override
	public Pagination getOrdersDetailListOld(Pagination page) {

		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT a.remarks,a.order_pack_id orderPackId, a.hosp_code hospCode, a.hosp_name hospName,  ");
		sb.append(" a.hosp_ar_fullname hospArFullname, a.count, a.amount, a.is_urgent isUrgent, ");
		sb.append(" DATE_FORMAT(a.submit_datetime, '%Y-%m-%d %T') submitDatetime,a.del_comp_code delCompCode,a.del_comp_name delCompName, ");
		sb.append(" a.recp_addr recpAddr, a.recp_userphone recpUserphone, a.recp_user recpUser,a.order_status orderStatus ");
		sb.append(" FROM pur_orderpack a WHERE  ");
		sb.append(" a.order_status = :orderStatus ");
		
		OrderPackage orderPackage = (OrderPackage) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("orderStatus", OrderPackStatus.finishOrder.getKey());
		
		if (StringUtils.isNotBlank(orderPackage.getHospName())) {
			sb.append(" AND ( a.hosp_name LIKE :hospName OR a.hosp_spel_code LIKE :hospName ) ");
			params.put("hospName", "%" + orderPackage.getHospName() + "%");
		}
		
		if (StringUtils.isNotBlank(orderPackage.getAreaId())) {
			sb.append(" AND a.hosp_area_id LIKE :areaId ");
			params.put("areaId", AreaUtil.escapeAreaId(orderPackage.getAreaId()));
		}
		
		if (StringUtils.isNotBlank(orderPackage.getOrderPackId())) {
			sb.append(" AND a.order_pack_id = :orderPackId ");
			params.put("orderPackId", orderPackage.getOrderPackId());
		}
		
		if (orderPackage.getStartDate() != null) {
			sb.append(" AND DATE_FORMAT(a.submit_datetime, '%Y-%m-%d') >= :startDate ");
			params.put("startDate", DateUtil.date(orderPackage.getStartDate()));
		}
		
		if (orderPackage.getEndDate() != null) {
			sb.append(" AND DATE_FORMAT(a.submit_datetime, '%Y-%m-%d') <= :endDate ");
			params.put("endDate", DateUtil.date(orderPackage.getEndDate()));
		}
		if(StringUtils.isNotBlank(orderPackage.getDelCompName())){
			sb.append(" and (a.del_comp_name like :delCompName or a.del_comp_name_spel like :delCompName )");
			params.put("delCompName", "%"+orderPackage.getDelCompName()+"%");
		}
		sb.append(" order by a.submit_datetime DESC ");
		return dao.getPageMapList(sb.toString(), params, page);
	}

	/**
	 * @category 历史订单查询[生产企业端]
	 * @author 应晓川
	 * @date 2015年7月10日
	 */
	@Override
	public Pagination getOrdersDetailListOldByProComp(Pagination page) {

		String prodCompcode = page.getConditions().get("prodCompcode");
		String drugCode = page.getConditions().get("drugCode");
		String drugName = page.getConditions().get("drugName");
		String hospName = page.getConditions().get("hospName");
		String delCompName = page.getConditions().get("delCompName");
		String hospAreaId = AreaUtil.getAreaStartCode(page.getConditions().get("area1"),page.getConditions().get("area2"),page.getConditions().get("area3"));
		
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT a.order_code orderCode, a.order_pack_id orderPackId, ");
		sb.append("     a.pro_count proCount, a.del_count delCount, a.pro_price proPrice,  ");
		sb.append("     a.join_plan_time joinPlanTime, a.join_plan_user joinPlanUser,  ");
		sb.append("     a.order_status orderStatus, a.confirm_remark confirmRemark,  ");
		sb.append("     a.goods_code goodsCode, a.area_name areaName, a.pro_count * a.pro_price orderMoney, ");
		sb.append("     a.ar_shortname arShortname, a.ar_fullname arFullname,  ");
		sb.append("     a.proj_id projId, a.proj_name projName, a.proj_starttime projStarttime,  ");
		sb.append("     a.proj_endtime projEndtime, a.proj_status projStatus,  ");
		sb.append("     a.drug_code drugCode, a.qualityspec_name drugName,  ");
		sb.append("     a.form_name formName, a.spec_name specName, a.bus_name busName,  ");
		sb.append("     a.factor, a.unit_name unitName, a.material_name materialName,  ");
		sb.append("     a.prod_compcode prodCompcode, a.prod_compname prodCompname,  ");
		sb.append("     a.prod_compstatus prodCompstatus, a.cat_comp_code catCompCode,  ");
		sb.append("     a.cat_comp_name catCompName, a.cat_comp_status catCompStatus,  ");
		sb.append("     a.use_range useRange, a.drug_attr drugAttr, a.goods_source goodsSource,  ");
		sb.append("     a.pattern_type patternType, a.is_healthcare isHealthcare,  ");
		sb.append("     a.is_agriculture isAgriculture, a.is_lowprice isLowprice,  ");
		sb.append("     a.is_smallprice isSmallprice, a.is_smallsource isSmallsource,  ");
		sb.append("     a.is_longtime_plan isLongtimePlan, a.drug_status drugStatus,  ");
		sb.append("     a.drug_cat drugCat, a.pro_price_limit proPriceLimit,  ");
		sb.append("     a.retail_price_limit retailPriceLimit, a.hang_price hangPrice,  ");
		sb.append("     a.ref_price refPrice, a.healthcare_price healthcarePrice,  ");
		sb.append("     a.pic_url picUrl, a.pic_short_url picShortUrl,a.drug_medicine drugMedicine, a.`status`,  ");
		sb.append("		b.hosp_ar_fullname hospArFullname,b.hosp_name hospName,b.hosp_code hospCode,b.del_comp_code delCompCode,b.del_comp_name delCompName ");
		sb.append(" 	FROM pur_orderinfo a left join pur_orderpack b on b.order_pack_id = a.order_pack_id  ");
		sb.append("		where a.prod_compcode = :prodCompcode and (a.order_status = :orderStatus1 or a.order_status = :orderStatus2 or a.order_status = :orderStatus3) ");
		
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("prodCompcode", prodCompcode);
		params.put("orderStatus1", OrderDetailStatus.decline.getKey());
		params.put("orderStatus2", OrderDetailStatus.arrival.getKey());
		params.put("orderStatus3", OrderDetailStatus.nonArrival.getKey());
		
		if (StringUtils.isNotBlank(drugName)) {
			sb.append(" AND (a.qualityspec_name LIKE :drugName OR a.qualityspec_name_spel LIKE :drugName) ");
			params.put("drugName", "%" + drugName + "%");
		}
		
		if (StringUtils.isNotBlank(drugCode)) {
			sb.append(" AND a.drug_code = :drugCode ");
			params.put("drugCode", drugCode);
		}
		if (StringUtils.isNotBlank(hospName)) {
			sb.append(" AND (b.hosp_name like :hospName or b.hosp_spel_code like :hospName) ");
			params.put("hospName", "%"+hospName+"%");
		}
		if (StringUtils.isNotBlank(delCompName)) {
			sb.append(" AND (b.del_comp_name like :delCompName or b.del_comp_name_spel like :delCompName)");
			params.put("delCompName", "%"+delCompName+"%");
		}
		if (StringUtils.isNotBlank(hospAreaId)) {
			sb.append(" AND b.hosp_area_id like :hospAreaId ");
			params.put("hospAreaId", hospAreaId+"%");
		}
		
		sb.append(" order by a.join_plan_time DESC ");
		return dao.getPageMapList(sb.toString(), params, page);
	}

	/**
	 * @category 历史订单查询[配送企业端]
	 * @author 应晓川
	 * @date 2015年7月18日
	 */
	@Override
	public Pagination getOrdersDetailListOldByComp(Pagination page) {
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT a.order_status orderStatus,a.remarks,a.order_pack_id orderPackId, a.hosp_code hospCode, a.hosp_name hospName,  ");
		sb.append(" a.hosp_ar_fullname hospArFullname, a.count, a.amount, a.is_urgent isUrgent, ");
		sb.append(" DATE_FORMAT(a.submit_datetime, '%Y-%m-%d %T') submitDatetime, ");
		sb.append(" a.recp_addr recpAddr, a.recp_userphone recpUserphone, a.recp_user recpUser ");
		sb.append(" FROM pur_orderpack a WHERE a.del_comp_code = :delCompCode ");
		sb.append(" AND a.order_status = :orderStatus ");
		
		OrderPackage orderPackage = (OrderPackage) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("delCompCode", orderPackage.getDelCompCode());
		params.put("orderStatus", OrderPackStatus.finishOrder.getKey());
		
		if (StringUtils.isNotBlank(orderPackage.getHospName())) {
			sb.append(" AND ( a.hosp_name LIKE :hospName OR a.hosp_spel_code LIKE :hospName ) ");
			params.put("hospName", "%" + orderPackage.getHospName() + "%");
		}
		
		if (StringUtils.isNotBlank(orderPackage.getAreaId())) {
			sb.append(" AND a.hosp_area_id LIKE :areaId ");
			params.put("areaId", AreaUtil.escapeAreaId(orderPackage.getAreaId()));
		}
		
		if (StringUtils.isNotBlank(orderPackage.getOrderPackId())) {
			sb.append(" AND a.order_pack_id = :orderPackId ");
			params.put("orderPackId", orderPackage.getOrderPackId());
		}
		
		if (orderPackage.getStartDate() != null) {
			sb.append(" AND DATE_FORMAT(a.submit_datetime, '%Y-%m-%d') >= :startDate ");
			params.put("startDate", DateUtil.date(orderPackage.getStartDate()));
		}
		
		if (orderPackage.getEndDate() != null) {
			sb.append(" AND DATE_FORMAT(a.submit_datetime, '%Y-%m-%d') <= :endDate ");
			params.put("endDate", DateUtil.date(orderPackage.getEndDate()));
		}
		sb.append(" order by a.submit_datetime DESC ");
		return dao.getPageMapList(sb.toString(), params, page);
	}

	/**
	 * @category 历史订单查询[医疗机构]
	 * @author 应晓川
	 * @date 2015年7月20日
	 */
	@SuppressWarnings("rawtypes")
	@Override
	public Pagination getOrdersDetailListOldByHosp(Pagination page) {

		String hospId = page.getConditions().get("hospId");
		String orderCode = page.getConditions().get("orderCode");
		String joinPlanTimeStr = page.getConditions().get("joinPlanTimeStr");// 采购开始日期
		String joinPlanTimeEnd = page.getConditions().get("joinPlanTimeEnd");// 采购结算日期
		String submitStartDatetime = page.getConditions().get(
				"submitStartDatetime");// 确认开始日期
		String submitEndDatetime = page.getConditions()
				.get("submitEndDatetime");// 确认结束日期
		String delDatetimeStr = page.getConditions().get("delDatetimeStr");// 配送开始时间
		String delDatetimeEnd = page.getConditions().get("delDatetimeEnd");// 配送结束时间
		String drugCode = page.getConditions().get("drugCode");
		String drugName = page.getConditions().get("drugName");
		String compName = page.getConditions().get("compName");
		String prodComp = page.getConditions().get("prodComp");
		String orderStatus = page.getConditions().get("orderStatus");
		String archiveType = page.getConditions().get("archiveType");

		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT SQL_CALC_FOUND_ROWS poi.order_code AS orderCode,                                     ");
		sql.append(" poi.order_id AS orderId,																	 ");
		sql.append("       shi.hosp_name AS hospName,                                                            ");
		sql.append("       da.ar_fullname AS arFullname,                                                    	 ");
		sql.append("       Date_format(poi.join_plan_time, '%Y-%m-%d %H:%i:%S') AS joinPlanTime,                 ");
		sql.append("       poi.pro_price AS proPrice,                                                            ");
		sql.append("       poi.pro_count AS proCount,                                                            ");
		sql.append("       (poi.pro_count * poi.pro_price) AS totalPrice,                                        ");
		sql.append("       sci.comp_name AS compName,                                                            ");
		sql.append("       sci.comp_id AS compId,                                                           	 ");
		sql.append("       Date_format(poi.submit_datetime, '%Y-%m-%d %H:%i:%S') AS submitDatetime,              ");
		sql.append("       Date_format(pdoi.del_datetime, '%Y-%m-%d %H:%i:%S') AS delDatetime,                   ");
		sql.append("       pdoi.del_count AS delCount,                                                           ");
		sql.append("       (pdoi.del_count*poi.pro_price) AS delTotalPrice,                                      ");
		sql.append("       Date_format(pdoi.recp_datetime, '%Y-%m-%d %H:%i:%S') AS recpDatetime,													 ");
		sql.append("       pdoi.recp_count AS recpCount, 														 ");
		sql.append("      (pdoi.recp_count*poi.pro_price) AS recpTotalPrice,     								 ");
		sql.append("       vd.drug_code AS drugCode,                                                             ");
		sql.append("       vd.drug_name AS drugName,                                                             ");
		sql.append("       vd.form_name AS formName,                                                             ");
		sql.append("       vd.spec_name AS specName,                                                             ");
		sql.append("       vd.factor,                                                                            ");
		sql.append("       vd.unit,                                                                              ");
		sql.append("       vd.material_name AS materialName,                                                     ");
		sql.append("       vd.comp_name AS prodComp ,                                                            ");
		sql.append("       vd.prod_comp_id AS prodCompId ,                                                       ");
		sql.append("       poi.goods_code AS goodsCode,                                                         ");
		sql.append("       poi.pro_price_limit AS proPriceLimit,                                                   ");
		sql.append("       bg.bak_drug_flag AS bakDrugFlag,                                                      ");
		sql.append("       poi.is_longtime_plan AS isLongtimePlan,                                                      ");
		sql.append("       poi.order_pack_id AS orderPackId,                                                     ");
		sql.append("       (CASE poi.order_status WHEN 1 THEN '已提交' WHEN 2 THEN '确认配送' WHEN 3 THEN '无法配送' WHEN 4 THEN '已配送' WHEN 5 THEN '已到货' WHEN 6 THEN '未到货' ELSE '其他' END )AS orderStatus,                                                     ");
		sql.append("       shi.hosp_id AS hospId,                                                                ");
		sql.append("       ds.dic_name AS dicName,														");
		sql.append("       vd.drug_id AS drugId                                                               ");
		sql.append("     FROM pur_orderinfo_old poi LEFT JOIN  pur_delorderinfo_old pdoi ON poi.order_id = pdoi.order_id ");
		sql.append("    left join std_hospinfo shi on poi.hosp_id = shi.hosp_id    ");
		sql.append("    left join view_drug vd on poi.drug_id = vd.drug_id         ");
		sql.append("    left join std_compinfo sci on poi.del_comp_id = sci.comp_id  ");
		sql.append(" 	left join base_goods bg on  poi.goods_code = bg.goods_code ");
		sql.append(" 	left join dic_area da on da.area_id = sci.area_id ");
		sql.append(" 	left join dic_sysdic ds  on poi.archive_type =ds.dic_code  AND ds.classify_code = 118  ");
		sql.append(" WHERE  shi.hosp_id = :hospId");
		if (StringUtils.isNotBlank(orderCode)) {
			sql.append(" AND poi.order_code = :orderCode ");
		}
		// 采购时间
		if (StringUtils.isNotBlank(joinPlanTimeStr)) {
			sql.append(" AND date(poi.join_plan_time) >= :joinPlanTimeStr");
		}
		if (StringUtils.isNotBlank(joinPlanTimeEnd)) {
			sql.append(" AND date(poi.join_plan_time) <= :joinPlanTimeEnd ");
		}
		// 确认时间
		if (StringUtils.isNotBlank(submitStartDatetime)) {
			sql.append(" AND date(poi.submit_datetime) >= :submitStartDatetime");
		}
		if (StringUtils.isNotBlank(submitEndDatetime)) {
			sql.append(" AND date(poi.submit_datetime) <= :submitEndDatetime ");
		}
		// 配送时间
		if (StringUtils.isNotBlank(delDatetimeStr)) {
			sql.append(" AND date(pdoi.del_datetime) >= :delDatetimeStr");
		}
		if (StringUtils.isNotBlank(delDatetimeEnd)) {
			sql.append(" AND date(pdoi.del_datetime) <= :delDatetimeEnd ");
		}
		if (StringUtils.isNotBlank(drugCode)) {
			sql.append(" AND vd.drug_code = :drugCode ");
		}
		if (StringUtils.isNotBlank(drugName)) {
			sql.append(" AND (vd.drug_name like :drugName or vd.drug_name_spel like :drugName or vd.drug_name_eng like :drugName )");
		}
		if (StringUtils.isNotBlank(compName)) {
			sql.append(" AND (sci.comp_name like :compName or sci.comp_spel_code like :compName )");
		}
		if (StringUtils.isNotBlank(prodComp)) {
			sql.append(" AND (vd.comp_name like :prodComp or vd.comp_spel_code like :prodComp )");
		}
		if (StringUtils.isNotBlank(orderStatus)) {
			sql.append(" AND poi.order_status = :orderStatus ");
		}
		if (StringUtils.isNotBlank(archiveType)) {
			sql.append(" AND poi.archive_type = :archiveType ");
		}
		sql.append(" order by  poi.join_plan_time desc ");
		Query query = sessionFactory.getCurrentSession().createSQLQuery(
				sql.toString());
		Query queryCount = sessionFactory.getCurrentSession().createSQLQuery(
				"SELECT found_rows() AS rowcount");
		query.setParameter("hospId", hospId);
		if (StringUtils.isNotBlank(orderCode)) {
			query.setParameter("orderCode", orderCode);
		}
		// 采购时间
		if (StringUtils.isNotBlank(joinPlanTimeStr)) {
			try {
				query.setDate("joinPlanTimeStr", new SimpleDateFormat(
						"yyyy-MM-dd HH:mm:ss").parse(joinPlanTimeStr
						+ " 00:00:00"));
			} catch (ParseException e) {
				e.printStackTrace();
			}

		}
		if (StringUtils.isNotBlank(joinPlanTimeEnd)) {
			try {
				query.setDate("joinPlanTimeEnd", new SimpleDateFormat(
						"yyyy-MM-dd HH:mm:ss").parse(joinPlanTimeEnd
						+ " 23:59:59"));
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}
		// 确认时间
		if (StringUtils.isNotBlank(submitStartDatetime)) {
			try {
				query.setDate("submitStartDatetime", new SimpleDateFormat(
						"yyyy-MM-dd HH:mm:ss").parse(submitStartDatetime
						+ " 00:00:00"));
			} catch (ParseException e) {
				e.printStackTrace();
			}

		}
		if (StringUtils.isNotBlank(delDatetimeStr)) {
			try {
				query.setDate("delDatetimeStr", new SimpleDateFormat(
						"yyyy-MM-dd HH:mm:ss").parse(delDatetimeStr
						+ " 23:59:59"));
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}
		// 配送时间
		if (StringUtils.isNotBlank(delDatetimeEnd)) {
			try {
				query.setDate("delDatetimeEnd", new SimpleDateFormat(
						"yyyy-MM-dd HH:mm:ss").parse(delDatetimeEnd
						+ " 00:00:00"));
			} catch (ParseException e) {
				e.printStackTrace();
			}

		}
		if (StringUtils.isNotBlank(submitEndDatetime)) {
			try {
				query.setDate("submitEndDatetime", new SimpleDateFormat(
						"yyyy-MM-dd HH:mm:ss").parse(submitEndDatetime
						+ " 23:59:59"));
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}
		if (StringUtils.isNotBlank(drugCode)) {
			query.setParameter("drugCode", drugCode);
		}
		if (StringUtils.isNotBlank(drugName)) {
			query.setParameter("drugName", "%" + drugName + "%");
		}
		if (StringUtils.isNotBlank(compName)) {
			query.setParameter("compName", "%" + compName + "%");
		}
		if (StringUtils.isNotBlank(prodComp)) {
			query.setParameter("prodComp", "%" + prodComp + "%");
		}
		if (StringUtils.isNotBlank(orderStatus)) {
			query.setParameter("orderStatus", orderStatus);
		}
		if (StringUtils.isNotBlank(archiveType)) {
			query.setParameter("archiveType", archiveType);
		}
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List list = query.list();
		BigInteger total = (BigInteger) queryCount.uniqueResult();
		page.setRecords(total.longValue());
		page.setRows(list);
		return page;
	}
}
